package com.qsw.fileoperatelib.excel;

import com.qsw.fileoperatelib.excel.bean.impl.IExportExcel;
import com.qsw.fileoperatelib.bean.TwoDimenArray;

import java.io.File;
import java.io.FileInputStream;
import java.io.IOException;
import java.io.InputStream;
import java.util.Calendar;
import java.util.LinkedList;
import java.util.List;

import jxl.Cell;
import jxl.Sheet;
import jxl.Workbook;
import jxl.WorkbookSettings;
import jxl.format.Colour;
import jxl.read.biff.BiffException;
import jxl.write.Label;
import jxl.write.WritableCellFormat;
import jxl.write.WritableFont;
import jxl.write.WritableSheet;
import jxl.write.WritableWorkbook;
import jxl.write.WriteException;

/**
 * Excel工具类
 */
public class ExcelUtil {
    private static final String TAG = "TAG_ExcelUtil";

    /**
     * Excel文件后缀
     */
    public static final String SUFFIX_FILE_EXCEL = ".xls";

    private static WritableFont mFontArial14 = null;
    private static WritableCellFormat mFormatArial14 = null;

    private static WritableFont mFontArial10 = null;
    private static WritableCellFormat mFormatArial10 = null;

    private static WritableFont mFontArial12 = null;
    private static WritableCellFormat mFormatArial12 = null;

    private final static String UTF8_ENCODING = "UTF-8";

    /**
     * 单元格的格式设置。字体大小、颜色、对齐方式、背景颜色等...
     */
    private static void format() throws WriteException {
        mFontArial14 = new WritableFont(WritableFont.ARIAL, 14, WritableFont.BOLD);
        mFontArial14.setColour(jxl.format.Colour.LIGHT_BLUE);
        mFormatArial14 = new WritableCellFormat(mFontArial14);
        // 对齐格式
        mFormatArial14.setAlignment(jxl.format.Alignment.CENTRE);
        // 设置边框
        mFormatArial14.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        // 背景色
        mFormatArial14.setBackground(jxl.format.Colour.VERY_LIGHT_YELLOW);

        mFontArial10 = new WritableFont(WritableFont.ARIAL, 10, WritableFont.BOLD);
        mFormatArial10 = new WritableCellFormat(mFontArial10);
        mFormatArial10.setAlignment(jxl.format.Alignment.CENTRE);
        mFormatArial10.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
        mFormatArial10.setBackground(Colour.GRAY_25);

        mFontArial12 = new WritableFont(WritableFont.ARIAL, 12);
        mFormatArial12 = new WritableCellFormat(mFontArial12);
        mFormatArial10.setAlignment(jxl.format.Alignment.CENTRE);
        mFormatArial12.setBorder(jxl.format.Border.ALL, jxl.format.BorderLineStyle.THIN);
    }

    /**
     * 初始化Excel表格
     *
     * @param fileWholePath 存放excel文件的路径（path/demo.xls）
     * @param sheetName     Excel工作表的表名
     * @param colNames      excel中包含的列名（可以有多个）
     */
    private static void checkExcel(String fileWholePath, String sheetName, List<String> colNames) throws WriteException, IOException {
        format();
        WritableWorkbook writableWorkbook = null;
        try {
            boolean result;
            File path = new File(fileWholePath.substring(0, fileWholePath.lastIndexOf(File.separator)));
            if (!path.exists()) {
                result = path.mkdirs();
                if (!result) {
                    return;
                }
            }
            File excelFile = new File(fileWholePath);
            if (excelFile.exists()) {
                return;
            }
            result = excelFile.createNewFile();
            if (!result) {
                return;
            }
            writableWorkbook = Workbook.createWorkbook(excelFile);
            // 设置表格的名字
            WritableSheet writableSheet = writableWorkbook.createSheet(sheetName, 0);
            // 添加各列名
            for (int col = 0; col < colNames.size(); col++) {
                writableSheet.addCell(new Label(col, 0, colNames.get(col), mFormatArial10));
            }
            // 设置行高
            writableSheet.setRowView(0, 340);
            writableWorkbook.write();
        } finally {
            if (writableWorkbook != null) {
                writableWorkbook.close();
            }
        }
    }

    /**
     * 将指定的List写入Excel中
     *
     * @param dataList         待写入的list
     * @param parentFolderPath 根文件夹所在目录
     * @param timestamp        用于创建文件夹及文件的时间标记
     * @return 是否成功写入
     */
    public static boolean writeToExcel(List<? extends IExportExcel> dataList, String parentFolderPath, long timestamp) throws WriteException, IOException {
        if (dataList == null || dataList.size() == 0) {
            return true;
        }
        return writeToExcel(dataList, parentFolderPath, timestamp, dataList.get(0).getClass().getSimpleName());
    }

    /**
     * 将指定的List写入Excel中
     *
     * @param dataList         待写入的list
     * @param parentFolderPath 根文件夹所在目录
     * @param timestamp        用于创建文件夹及文件的时间标记
     * @param signName         文件命名特征
     * @return 是否成功写入
     */
    public static boolean writeToExcel(List<? extends IExportExcel> dataList, String parentFolderPath, long timestamp, String signName) throws WriteException, IOException {
        if (dataList == null || dataList.size() == 0) {
            return true;
        }
        return writeToExcel(dataList, createWholePath(parentFolderPath, timestamp, signName), signName);
    }

    /**
     * 将指定的List写入Excel中
     *
     * @param dataList      待写入的list
     * @param fileWholePath 文件完整路径
     * @param sheetName     Excel工作表的表名
     * @return 是否成功写入
     */
    public static boolean writeToExcel(List<? extends IExportExcel> dataList, String fileWholePath, String sheetName) throws WriteException, IOException {
        if (dataList == null || dataList.size() == 0) {
            return true;
        }

        checkExcel(fileWholePath, sheetName, dataList.get(0).getColumnName());

        WritableWorkbook writableWorkbook = null;
        InputStream is = null;
        Workbook workbook = null;
        try {
            WorkbookSettings setEncode = new WorkbookSettings();
            setEncode.setEncoding(UTF8_ENCODING);

            is = new FileInputStream(new File(fileWholePath));
            workbook = Workbook.getWorkbook(is);
            writableWorkbook = Workbook.createWorkbook(new File(fileWholePath), workbook);
            WritableSheet writableSheet = writableWorkbook.getSheet(0);
            List<String> list;

            for (int i = 0; i < dataList.size(); i++) {
                if (dataList.get(i) == null) {
                    continue;
                }
                list = dataList.get(i).toList();
                for (int j = 0; j < list.size(); j++) {
                    writableSheet.addCell(new Label(j, i + 1, list.get(j), mFormatArial12));
                    // 设置列宽
                    if (list.get(j).length() <= 4) {
                        writableSheet.setColumnView(j, list.get(j).length() + 8);
                    } else {
                        writableSheet.setColumnView(j, list.get(j).length() + 5);
                    }
                }
                // 设置行高
                writableSheet.setRowView(i + 1, 350);
            }
            writableWorkbook.write();
        } catch (Exception e) {
            e.printStackTrace();
            return false;
        } finally {
            if (workbook != null) {
                workbook.close();
            }
            if (writableWorkbook != null) {
                writableWorkbook.close();
            }
            if (is != null) {
                is.close();
            }
        }
        return true;
    }

    /**
     * 读取Excel
     *
     * @param excelFile excel文件
     * @param cls       要承载的IExportExcel子类
     * @param <E>       要承载的IExportExcel子类类型
     * @return 结果
     * @throws BiffException
     * @throws IOException
     * @throws IllegalAccessException
     * @throws InstantiationException
     */
    public static <E extends IExportExcel> List<E> readFromExcel(File excelFile, Class<E> cls) throws BiffException, IOException, IllegalAccessException, InstantiationException {
        TwoDimenArray<String> stringTwoDimenArray = readFromExcel(excelFile);
        List<E> result = new LinkedList<>();
        if (stringTwoDimenArray != null) {
            String[][] dimenArrayArray = stringTwoDimenArray.getArray();
            for (int i = 1; i < dimenArrayArray.length; i++) {
                E e = cls.newInstance();
                e.fromList(dimenArrayArray[i]);
                result.add(e);
            }
        }
        return result;
    }

    /**
     * 读取Excel
     *
     * @param excelFile excel文件
     */
    public static TwoDimenArray<String> readFromExcel(File excelFile) throws IOException, BiffException {
        if (!excelFile.exists()) {
            return null;
        }
        InputStream is;
        Workbook workbook = null;

        is = new FileInputStream(excelFile);
        workbook = Workbook.getWorkbook(is);

        if (workbook == null) {
            return null;
        }

        // 获取sheet, 如果workbook里有多个sheet可以利用workbook.getSheets()方法来得到所有的
        Sheet[] sheets = workbook.getSheets();
        if (sheets == null || sheets.length == 0) {
            return null;
        }
        Sheet sheet = sheets[0];// 这里只取得第一个sheet的值，默认从0开始
//        System.out.println(sheet.getColumns());// 查看sheet的列
//        System.out.println(sheet.getRows());// 查看sheet的行

        int column = sheet.getColumns();
        int row = sheet.getRows();

        String[][] resultArray = new String[row][column];

        Cell cell;// 单个单元格
        for (int i = 0; i < row; i++) {
            for (int j = 0; j < column; j++) {
                // 列，行
                cell = sheet.getCell(j, i);
                resultArray[i][j] = cell.getContents();
            }
        }

        workbook.close();// 关闭工作簿

        return new TwoDimenArray<>(resultArray, column, row);
    }

    /**
     * 根据当前时间生成一个指定的Excel文件名及完整路径
     *
     * @param parentFolderPath 根文件夹所在目录
     * @param timestamp        用于创建文件夹及文件的时间标记
     * @param obj              文件类实例
     * @return 生成的文件名及完整路径
     */
    public static String createWholePath(String parentFolderPath, long timestamp, Object obj) {
        return createWholePath(parentFolderPath, timestamp, obj.getClass().getSimpleName());
    }

    /**
     * 根据当前时间生成一个指定的Excel文件名及完整路径
     *
     * @param parentFolderPath 根文件夹所在目录
     * @param timestamp        用于创建文件夹及文件的时间标记
     * @param cls              文件类
     * @return 生成的文件名及完整路径
     */
    public static String createWholePath(String parentFolderPath, long timestamp, Class cls) {
        return createWholePath(parentFolderPath, timestamp, cls.getSimpleName());
    }

    /**
     * 根据当前时间生成一个指定的Excel文件名及完整路径
     *
     * @param parentFolderPath 根文件夹所在目录
     * @param timestamp        用于创建文件夹及文件的时间标记
     * @param sign             文件名的特殊标记
     * @return 生成的文件名及完整路径
     */
    public static String createWholePath(String parentFolderPath, long timestamp, String sign) {
        int[] dateTime24 = timestampToDateTime24(timestamp);
        return createFilePath(parentFolderPath, timestamp) + File.separator
                + (dateTime24[0] < 10 ? "0" : "") + dateTime24[0]
                + (dateTime24[1] < 10 ? "0" : "") + dateTime24[1]
                + (dateTime24[2] < 10 ? "0" : "") + dateTime24[2]
                + "_"
                + (dateTime24[3] < 10 ? "0" : "") + dateTime24[3]
                + (dateTime24[4] < 10 ? "0" : "") + dateTime24[4]
                + (dateTime24[5] < 10 ? "0" : "") + dateTime24[5]
                + "_" + sign
                + SUFFIX_FILE_EXCEL;
    }

    /**
     * 根据参数生成一个根文件夹路径
     *
     * @param parentFolderPath 根文件夹所在目录
     * @param timestamp        用于创建文件夹的时间标记
     * @return 文件路径
     */
    public static String createFilePath(String parentFolderPath, long timestamp) {
        return parentFolderPath + File.separator + timestamp;
    }

    /**
     * 时间戳转换为年月日时分秒，月份已转换为正常数字，时间为24小时制
     *
     * @param timestamp 时间戳
     * @return 共6个值，年、月、日、时、分、秒索引分别为0 - 5
     */
    private static int[] timestampToDateTime24(long timestamp) {
        int[] dateTime24 = timestampToOriginalDateTime24(timestamp);
        dateTime24[1] += 1;
        return dateTime24;
    }

    /**
     * 时间戳转换为年月日时分秒，时间为24小时制
     *
     * @param timestamp 时间戳
     * @return 共6个值，年、月、日、时、分、秒索引分别为0 - 5
     */
    private static int[] timestampToOriginalDateTime24(long timestamp) {
        Calendar calendar = Calendar.getInstance();
        calendar.setTimeInMillis(timestamp);
        return new int[]{calendar.get(Calendar.YEAR),
                calendar.get(Calendar.MONTH),
                calendar.get(Calendar.DAY_OF_MONTH),
                calendar.get(Calendar.HOUR_OF_DAY),
                calendar.get(Calendar.MINUTE),
                calendar.get(Calendar.SECOND)};
    }
}
